https://prefect.io logo
Join Slack
Powered by
<@ULVA73B9P> I am self hosting prefect on docker d...
# ask-marvin
o
@Marvin I am self hosting prefect on docker desktop, and recently the Prefect Web UI has become a lot slower, and the prefect server container a lot more demanding (docker stats currently shows ~5GB of ram consumption by the server, and it spikes higher). I have tried doing some light maintenance on my instance (restarting service, deleting old flow runs, VACUUMing SQLite database, imposing environment limits on retention period in docker compose, etc). Any tips?
m
✅ thought for 370.5 seconds
Thanks for the detail — that helps. A few common causes for slow UI and high RAM on a self‑hosted server (especially on Docker Desktop with SQLite) and the quickest fixes: Assumptions: You’re on Prefect 3.x and currently using SQLite (since you VACUUM’d a SQLite DB). If that’s wrong, let me know. Top culprits and immediate fixes - Large/old events history causing big queries and UI websocket backfill - Set a shorter events retention window (2–3 days if you have high volume) - 
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
- Reduce the UI websocket backfill window and page size (speeds up UI load + lowers memory) - 
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
- 
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
- Optionally shorten the related resource cache TTL to keep memory in check - 
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S
- Docs for these settings: Server Events settings - SQLite itself - SQLite is fine for local/dev but struggles under load (locking, big queries, no pooling). If you’re seeing 5GB+ and spikes, the single biggest improvement is switching to Postgres. - Docs: Scale self-hosted Prefect and Server Database settings - Services not running - Make sure you are not starting the API with 
--no-services
. The background services (including event cleanup) should be running. - CLI help shows 
--no-services
exists; avoid it unless you’re running services in a separate container. - CLI: 
prefect server start --help
confirms options. Recommended next steps (fastest path) 1) Try these environment settings first (keep your current stack) - Add these to your docker-compose env for the server, then restart:
Copy code
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S
- Ensure you’re starting the server with services enabled (don’t use 
--no-services
unless you run a separate “services” container). 2) Strongly consider moving from SQLite to Postgres - Minimal docker-compose change: - Add a 
postgres
service - Point Prefect at it via 
PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
- Let the server migrate the DB on start with 
PREFECT_API_DATABASE_MIGRATE_ON_START=true
- Example snippet:
Copy code
services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_USER: prefect
      POSTGRES_PASSWORD: prefect
      POSTGRES_DB: prefect
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U prefect"]
      interval: 5s
      timeout: 5s
      retries: 5
    ports:
      - "5432:5432"

  prefect-server:
    image: prefecthq/prefect:3-latest
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
      PREFECT_API_DATABASE_MIGRATE_ON_START: "true"
      # keep the events tweaks from above here as well
      PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D"
      PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S"
      PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100"
      PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S"
    command: prefect server start --host 0.0.0.0
    ports:
      - "4200:4200"

volumes:
  postgres_data:
- After switching to Postgres, you’ll gain proper connection pooling and more predictable performance under load. - Server database settings: Settings reference — database
3) If you later scale up or split services, consider Redis for event messaging/caching - Useful when you run multiple API replicas; for a single container, the default in‑memory broker/cache is fine. - If you do add Redis, you’ll set:
Copy code
PREFECT_MESSAGING_BROKER=prefect.server.utilities.messaging.memory   # keep memory for single container
PREFECT_MESSAGING_CACHE=prefect.server.utilities.messaging.memory    # keep memory for single container
- Only switch to the Redis implementations in multi‑server setups. Useful CLI while tuning - Upgrade DB (if needed during version changes):
Copy code
prefect server database upgrade -y
- Reset DB (destructive):
Copy code
prefect server database reset -y
- CLI reference we verified: - 
prefect server database upgrade --help
- 
prefect server database reset --help
- 
prefect server start --help
A couple of clarifying questions to tailor this further - Which Prefect version are you on (e.g., 3.1.x)? If not current, upgrading can include performance fixes. Release notes: Changelog - Roughly how many flow runs per day and how “chatty” are your logs? - Can you share your current docker-compose for the Prefect services? I can mark up the exact env vars to add. - Are you starting 
prefect server start
with services enabled, or are you running services separately? If you paste your compose file and the size of your events/log tables (or just total DB size), I’ll help you make the minimal changes to get memory usage back under control.
o
1. I am on prefect 3.4.17, so not latest but pretty recent. 2. Chattyness varies a lot - some flows are very chatty with logs and have up to ~5000 tasks. Some flows are very small / simple (these run the most often). Flows with "real work" perhaps ~6 a day, including the smaller once a lot hgiher (some run every half hour). 3. I have added, but not yet run, your recommended changes (ie no docker compose down / up yet) 4. If possible, I'd like to keep logs/deploys/etc from the old sqlite db when migrating to postgres. 5. Current compose, after adding postgres: # (top-level) optional but recommended to avoid project-name drift name: qfin services: postgres: image: postgres:15-alpine environment: POSTGRES_USER: prefect POSTGRES_PASSWORD: prefect POSTGRES_DB: prefect volumes: - postgres_data:/var/lib/postgresql/data healthcheck: test: ["CMD-SHELL", "pg_isready -U prefect"] interval: 5s timeout: 5s retries: 5 ports: - "5432:5432" prefect-server: image: prefecthq/prefect:3-latest depends_on: postgres: condition: service_healthy restart: always ports: - "4200:4200" environment: PREFECT_API_DATABASE_CONNECTION_URL: postgresql+asyncpg://prefect:prefect@postgres:5432/prefect PREFECT_API_DATABASE_MIGRATE_ON_START: "true" PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D" PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S" PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100" PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S" command: prefect server start --host 0.0.0.0 healthcheck: test: ["CMD", "python", "-c", "import urllib.request; urllib.request.urlopen('http://127.0.0.1:4200/api/health', timeout=5).read()"] interval: 30s timeout: 10s retries: 3 start_period: 40s volumes: # persist Prefect's local data/db - prefect_data:/root/.prefect create-work-pools: # Run once to create work pools, container dies after creating pools image: prefecthq/prefect:3-latest (...) prefect-prod-worker: image: prefecthq/prefect:3-latest (...) prefect-dev-worker: image: prefecthq/prefect:3-latest (...) volumes: prefect_data: name: brrr_prefect_data postgres_data: the old prefect_data volumes is where my sqlite have been living. I have shortened compose for other services to not break length limit @Marvin
m
Your message was too long, here's your message at the allowed limit: 1 I am on prefect 3 4 17 so not latest but pretty recent 2 Chattyness varies a lot - some flows are very chatty with logs and have up to ~5000 tasks Some flows are very small / simple (these run the most often) Flows with real work perhaps ~6 a day including the smaller once a lot hgiher (some run every half hour) 3 I have added but not yet run your recommended changes (ie no docker compose down / up yet) 4 If possible I'd like to keep logs/deploys/etc from the old sqlite db when migrating to postgres 5 Current compose after adding postgres # (top-level) optional but recommended to avoid project-name drift name qfin services postgres image postgres 15-alpine environment POSTGRES_USER prefect POSTGRES_PASSWORD prefect POSTGRES_DB prefect volumes - postgres_data /var/lib/postgresql/data healthcheck test [ CMD-SHELL pg_isready -U prefect ] interval 5s timeout 5s retries 5 ports - 5432 5432 prefect-server image prefecthq/prefect 3-latest depends_on postgres condition service_healthy restart always ports - 4200 4200 environment PREFECT_API_DATABASE_CONNECTION_URL <postgresql+asyncpg //prefect prefect@postgres 5432/prefect> PREFECT_API_DATABASE_MIGRATE_ON_START true PREFECT_SERVER_EVENTS_RETENTION_PERIOD P2D PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL PT300S PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE 100 PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL PT300S command prefect server start --host 0 0 0 0 healthcheck test [ CMD python -c import urllib request; urllib request urlopen('<http //127 0 0 1 4200/api/health>' timeout=5) read() ] interval 30s timeout 10s retries 3 start_period 40s volumes # persist Prefect's local data/db - prefect_data /root/ prefect create-work-pools # Run once to create work pools container dies after creating pools image prefecthq/prefect 3-latest depends_on prefect-server condition service_healthy environment PREFECT_API_URL <http //prefect-server 4200/api> restart no entrypoint > sh -c echo 'Waiting for Prefect server to be ready ' && until prefect work-pool ls; do echo 'Waiting for Prefect server ' && sleep 5 done && echo 'Creating work pools ' && prefect work-pool create 'production-pool' --type docker || echo 'Production pool already exists' && prefect work-pool create 'dev-pool' --type docker || echo 'Dev pool already exists' && echo 'Work pools created successfully ' && prefect work-pool ls && sleep 10 prefect-prod-worker image prefecthq/prefect 3-latest restart always environment PREFECT_API_URL <http //prefect-server 4200/api> PREFECT_WORK_POOL production-pool DOCKER_HOST unix ///var/run/docker sock command > sh -c python -m pip install --no-cache-dir -q prefect-docker docker || true && echo 'Waiting for Prefect API ' && until prefect work-pool ls >/dev/null 2>&1; do echo 'Waiting for Prefect server ' && sleep 3 done && echo \ Waiting for pool $${PREFECT_WORK_POOL} \ && until prefect work-pool inspect \ $${PREFECT_WORK_POOL}\ >/dev/null 2>&1; do echo \ Pool $${PREFECT_WORK_POOL} not ready yet \ && sleep 3 done && echo \ Starting worker for pool $${PREFECT_WORK_POOL} \ && prefect worker start -p \ $${PREFECT_WORK_POOL}\ depends_on prefect-server condition service_healthy create-work-pools condition service_completed_successfully volumes # Allow prefect workers to spin up containers by mounting the Unix socket Docker Engine uses to accept API commands - /var/run/docker sock /var/run/docker sock prefect-dev-worker image prefecthq/prefect 3-latest restart always environment PREFECT_API_URL <http //prefect-server 4200/api> PREFECT_WORK_POOL dev-pool DOCKER_HOST unix ///var/run/docker sock command > sh -c python -m pip install --no-cache-dir -q prefect-docker docker || true && echo 'Waiting
✅ thought for 310.3 seconds
Awesome — thanks for the detail. Given your workload (some very chatty flows, thousands of tasks, and steady cadence), the two biggest wins will be: - shorten events backfill/retention (you added these — great), and - move from SQLite to Postgres while preserving your data. Below is a safe, minimal‑downtime migration plan that keeps your deployments, flow/task runs, logs, events, automations, etc. Important compose fixes first - In your compose, remove angle brackets around values. These two should be plain strings: - 
PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
- Healthcheck URL should be 
<http://127.0.0.1:4200/api/health>
(no 
< >
). - Pin the Prefect image to match your server version during migration to avoid unplanned upgrades: - 
image: prefecthq/prefect:3.4.17
Migration runbook (SQLite → Postgres with data preserved) 0) Quiesce and back up - Stop writes: - Temporarily scale workers to 0 or stop them so no new runs/logs are written during the move. - Back up SQLite file from your volume:
Copy code
docker run --rm -v brrr_prefect_data:/data -v $(pwd):/backup alpine sh -c "cp /data/prefect.db /backup/prefect.db.bak"
The SQLite file lives at 
/root/.prefect/prefect.db
inside the container, which maps to your 
brrr_prefect_data
volume. 1) Start Postgres and enable pg_trgm - Bring up Postgres only:
Copy code
docker compose up -d postgres
- Enable pg_trgm (recommended for search performance):
Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
2) Migrate data with pgloader - Use pgloader (best compatibility from SQLite → Postgres). This runs in the same Docker network and mounts your SQLite file:
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  dimitri/pgloader:latest \
  pgloader sqlite:///root/.prefect/prefect.db <postgresql://prefect:prefect@postgres/prefect>
- Verify a few table counts (optional sanity checks):
Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
3) Point Prefect at Postgres and apply migrations - Keep the events tuning you added:
Copy code
PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
PREFECT_API_DATABASE_MIGRATE_ON_START=true
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S
- Bring up the server:
Copy code
docker compose up -d prefect-server
With 
PREFECT_API_DATABASE_MIGRATE_ON_START=true
, Prefect will apply any Postgres‑specific schema migrations on startup. If you prefer to do it explicitly:
Copy code
docker exec -it $(docker ps -qf "name=prefect-server") prefect server database upgrade -y
- Bring workers back up once the API is healthy. 4) Post‑migration checks - UI loads quickly, lists of flow runs / deployments appear - A few old flow runs show logs - New runs write logs and events normally Notes on performance with your workload - You’ve already added the most impactful event settings: - 
P2D
retention, shorter websocket backfill and page size. - Once on Postgres, connection pooling behaves much better. If you still see pressure, you can tune pool size/overflow:
Copy code
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=10
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=5
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=30
- Very chatty flows: consider trimming log verbosity where possible; logs and events volume directly affects UI query time and DB size. Links you may find handy - Server events settings (retention, websocket backfill, cache): Settings reference — server events - Database settings and connection string: Settings reference — database - Server CLI and database commands: Self-hosting with the Server CLI Want me to mark up your full compose (including workers) with exact changes and pinned versions before you do 
docker compose down && up -d
? If you paste the omitted parts, I’ll annotate it so you can run it as-is.
o
@Marvin olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker run --rm --network qfin_default \ -v brrr_prefect_data:/root/.prefect \ dimitri/pgloader:latest \ pgloader sqlite:///root/.prefect/prefect.db postgresql://prefect:prefect@postgres/prefect Unable to find image 'dimitri/pgloader:latest' locally latest: Pulling from dimitri/pgloader 59ce1b6c0c82: Pull complete 7422dc743b5a: Pull complete 3ab3a7d17840: Pull complete 8d9a861a619b: Pull complete Digest: sha256:756758ca8269404bdca9384c2fb04bcc7de744a713d26885ba8b556e4005eab4 Status: Downloaded newer image for dimitri/pgloader:latest 2025-11-19T225831.041366Z LOG pgloader version "3.6.7~devel" 2025-11-19T225831.188028Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///root/.prefect/prefect.db {10072F8D03}> 2025-11-19T225831.191788Z LOG Migrating into #<PGSQL-CONNECTION pgsql://prefect@postgres:5432/prefect {1007445123}> 2025-11-19T225831.816040Z ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "strftime('%Y-%m-%d %H:%M:%f000', 'now')" QUERY: CREATE TABLE flow ( id text default '( lower(hex(randomblob(4))) || ''-'' || lower(hex(randomblob(2))) || ''-4'' || substr(lower(hex(randomblob(2))),2) || ''-'' || substr(''89ab'',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || ''-'' || lower(hex(randomblob(6))) )', created timestamptz default 'strftime(''%Y-%m-%d %H:%M:%f000'', ''now'')', updated timestamptz default 'strftime(''%Y-%m-%d %H:%M:%f000'', ''now'')', name text, tags json default '[]', labels json ); 2025-11-19T225831.816040Z FATAL Failed to create the schema, see above. 2025-11-19T225831.823561Z LOG report summary reset table name errors rows bytes total time ----------------- --------- --------- --------- -------------- fetch 0 0 0.000s fetch meta data 0 256 0.369s Create Schemas 0 0 0.004s Create SQL Types 0 0 0.026s Create tables 0 0 0.000s ----------------- --------- --------- --------- -------------- ----------------- --------- --------- --------- --------------
m
✅ thought for 80.3 seconds
Great data point — that pgloader error is exactly what happens when it tries to CREATE the schema from SQLite DDL. SQLite uses 
strftime(...)
defaults that Postgres doesn’t understand, so we need to: - create the Postgres schema with Prefect first, then - run pgloader in data-only mode and exclude 
alembic_version
. Here’s the quickest path forward. Why it failed - pgloader attempted to create tables in Postgres from the SQLite schema, which contains SQLite-only default expressions like 
strftime(...)
. Postgres rejects those defaults, so the schema creation fails. Correct approach 1) Stop Prefect (server and workers) so nothing writes during migration. 2) Create the Postgres schema with Prefect - Run migrations once against Postgres to create the correct schema:
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database upgrade -y
3) Use pgloader in data-only mode and exclude alembic_version - Create a file called 
pgloader.load
in your current directory with this content:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres/prefect>
 WITH data only,
      batch rows = 5000,
      prefetch rows = 5000,
      on error stop
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
- Then run pgloader and mount both the SQLite volume and the load file:
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Why we exclude alembic_version - The migration you ran in step 2 created the Postgres schema and wrote Postgres’s own migration revision into 
alembic_version
. - If you copy the SQLite 
alembic_version
, you’ll end up with a mismatched revision and future migrations could fail. 4) Sanity checks - Spot-check a few counts after pgloader finishes:
Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
5) Start Prefect against Postgres - In your compose: - Remove the angle brackets from the connection URL. - Pin the image during migration: 
prefecthq/prefect:3.4.17
. - Keep the event settings you added. - Bring the server back up, then the workers. - If you want to be extra safe, run:
Copy code
docker exec -it $(docker ps -qf "name=prefect-server") prefect server database upgrade -y
It should be a quick no-op. If pgloader throws data casting errors - JSON columns: your Prefect JSON/JSONB columns typically contain valid JSON; pgloader will usually cast fine. If it complains, we can add a CAST rule to the load file. Share the error and I’ll give you the exact CAST line. - Foreign keys: pgloader usually orders inserts to satisfy FKs; if not, we can temporarily disable constraints, but that’s rarely needed for Prefect. After you’re up - Your new Postgres-backed server + the event settings should materially reduce RAM spikes and speed up the UI. - You can later tune pool sizing if needed:
Copy code
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=10
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=5
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=30
Want me to review your full compose (server + workers) and pin/clean it before you flip over? If you paste the omitted parts, I’ll annotate and hand back a ready-to-run file.
o
@Marvin docker run --rm --network qfin_default \ -e PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://prefect:prefect@postgres:5432/prefect \ prefecthq/prefect:3-latest prefect server database upgrade -y docker: Error response from daemon: failed to set up container networking: network qfin_default not found
m
✅ thought for 32.4 seconds
That error just means the Compose network doesn’t exist yet. You’ve got two easy options to proceed: Option A — Use the Compose network (qfin_default) 1) Create the network by starting Postgres from your compose:
Copy code
docker compose up -d postgres
2) Re-run the migration on that network (remove the angle brackets and pin Prefect to 3.4.17):
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database upgrade -y
3) Run pgloader in data-only mode (after creating a load file — see below). Option B — Skip the Compose network and connect via the published host port Since Postgres is published on 5432, just connect to the host from the container. On Docker Desktop (Windows/macOS), use host.docker.internal: - Create schema with Prefect migrations:
Copy code
docker run --rm \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@host.docker.internal:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database upgrade -y
- Run pgloader (data-only) using host.docker.internal as the target: Create pgloader.load in your working dir:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@host.docker.internal:5432/prefect>
 WITH data only,
      batch rows = 5000,
      prefetch rows = 5000,
      on error stop
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
Then run:
Copy code
docker run --rm \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Notes and small fixes - Remove angle brackets from your env values. Use: - 
PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
- Pin Prefect image to your running version during migration: 
prefecthq/prefect:3.4.17
- If you prefer using Compose for the migration, add a one-off service:
Copy code
migrate:
  image: prefecthq/prefect:3.4.17
  depends_on:
    postgres:
      condition: service_healthy
  environment:
    PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
  command: prefect server database upgrade -y
Then:
Copy code
docker compose run --rm migrate
After pgloader finishes - Sanity-check a few counts:
Copy code
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
- Start your Prefect server with the Postgres URL and the event settings you added. - Bring workers back up. References - Server CLI (database commands): Self-hosted Server CLI - Database settings (connection URL and timeouts): Settings reference — database - Events settings (retention/backfill): Settings reference — server events If you want, paste your full compose and I’ll mark it up so you can run it end-to-end without switching networks mid-process.
o
@Marvin running docker run --rm --network qfin_default -v brrr_prefect_data:/root/.prefect -v $(pwd)/pgloader.load:/load.load dimitri/pgloader:latest pgloader /load.load prints a lot of warrnings such as 2025-11-19T231750.799068Z WARNING Source column "public"."deployment_version"."entrypoint" is casted to type "text" which is not the same as "character varying", the type of current target database column "public"."deployment_version"."entrypoint". (for many columns) as well as some sqlite specific such as 2025-11-19T231750.806826Z ERROR pgloader failed to find target table for source NIL."sqlite_stat1" with name "sqlite_stat1" in target catalog also get some errors on keys 2025-11-19T231751.349881Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_configuration__key" DETAIL: Key (key)=(TELEMETRY_SESSION) already exists. CONTEXT: COPY configuration, line 1 (among others)
m
✅ thought for 38.0 seconds
Thanks for sharing the output — super helpful. What you’re seeing is expected with a first pass: - The varchar vs text warnings are harmless. Postgres will coerce text into varchar (unless there’s a length limit, which Prefect doesn’t use here). - The sqlite_stat1 error is pgloader trying to copy SQLite’s internal tables; we should exclude them. - The duplicate key in configuration is because we created the Postgres schema/migrations first (which seed some configuration rows), then pgloader tried to insert those same rows. Let’s tighten the pgloader config to avoid these and rerun. 1) Keep the Postgres schema created by Prefect You already ran the migration to create the schema. Good — keep that. 2) Use a more specific pgloader.load Update your load file to exclude SQLite internal tables and avoid duplicates by truncating the small, seeded configuration table first. Create/replace pgloader.load with:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres/prefect>

 WITH data only,
      batch rows = 5000,
      prefetch rows = 5000,
      on error stop

 -- Exclude SQLite internal tables and Alembic version
 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'

 -- Truncate small seeded table to avoid duplicate unique keys
 BEFORE LOAD DO
  $$ TRUNCATE TABLE configuration; $$
;
3) Run pgloader again
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
4) If you see any other duplicate key errors - It’s typically just 
configuration
. If pgloader reports duplicates on another small seeded table, you can add another TRUNCATE in the “BEFORE LOAD DO” block, e.g.:
Copy code
BEFORE LOAD DO
  $$ TRUNCATE TABLE configuration; $$
  $$ TRUNCATE TABLE <table_name>; $$
Paste the exact error and I’ll give you the exact line(s) to add. 5) Sanity check
Copy code
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") \
  psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
6) Start Prefect against Postgres - In your compose, ensure: - No angle brackets in the URL - Pin the image for the migration: 
prefecthq/prefect:3.4.17
- Keep the event settings you added - Bring up the server, then workers. If you want, run a final no-op migration:
Copy code
docker exec -it $(docker ps -qf "name=prefect-server") prefect server database upgrade -y
Reference - Server CLI (database commands): Self-hosted Server CLI - Database settings: Settings reference — database - Events settings (retention/backfill): Settings reference — server events If the next pgloader run throws a different constraint error, paste it here and I’ll adjust the load file precisely (either TRUNCATE the target table or exclude it if it’s safe to keep Postgres’s seeded values).
o
@Marvin lots of warnings on columns remain, but also more severe looking stuff 2025-11-19T232620.306892Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_flow" DETAIL: Key (id)=(1e44683c-a3b2-452b-925c-da74daebd1e2) already exists. CONTEXT: COPY flow, line 1 2025-11-19T232620.364549Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_concurrency_limit" DETAIL: Key (id)=(0e3c7b58-c543-4aba-87df-b386d09ba8b6) already exists. CONTEXT: COPY concurrency_limit, line 1 2025-11-19T232620.599021Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_block_schema_reference" DETAIL: Key (id)=(2d6c80e4-870b-41a0-bd14-d423e5c2cd1e) already exists. CONTEXT: COPY block_schema_reference, line 1 2025-11-19T232620.606709Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_block_schema__checksum_version" DETAIL: Key (checksum, version)=(sha256:61e9dbea14935ccb2cfac7eb38f01c4e878a81073806f2aec993820fa0d91eb3, 3.4.17) already exists. CONTEXT: COPY block_schema, line 1 2025-11-19T232620.748930Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_block_document_reference" DETAIL: Key (id)=(3f615161-685e-4f14-af19-5c8658d80ccd) already exists. CONTEXT: COPY block_document_reference, line 1 2025-11-19T232620.748930Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_block_document" DETAIL: Key (id)=(e4c96407-768c-4b88-acbd-2265f448c5f3) already exists. CONTEXT: COPY block_document, line 1 2025-11-19T232620.806587Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_worker" DETAIL: Key (id)=(47b18cd5-76c4-42a5-a718-94e3530d026b) already exists. CONTEXT: COPY worker, line 1 2025-11-19T232620.814274Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_block_type__slug" DETAIL: Key (slug)=(webhook) already exists. CONTEXT: COPY block_type, line 1 2025-11-19T232621.075652Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_flow_run_state" DETAIL: Key (id)=(01993eaf-d045-77b0-b695-1ee3e0b4640f) already exists. CONTEXT: COPY flow_run_state, line 1 2025-11-19T232621.164059Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_work_pool__name" DETAIL: Key (name)=(production-pool) already exists. CONTEXT: COPY work_pool, line 1 2025-11-19T232625.556442Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_log" DETAIL: Key (id)=(01993eaf-ec3b-7d7d-b153-47a9f5cc9cf7) already exists. CONTEXT: COPY log, line 1 2025-11-19T232630.599636Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_task_run_state" DETAIL: Key (id)=(019956d5-40f5-7bb8-a0ac-a79f661c9969) already exists. CONTEXT: COPY task_run_state, line 1 2025-11-19T232630.645800Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_work_queue" DETAIL: Key (id)=(09012307-9413-41d3-90c0-33005637bd37) already exists. CONTEXT: COPY work_queue, line 1 Heap exhausted during garbage collection: 1456 bytes available, 4496 requested .... fatal error encountered in SBCL pid 1 tid 9: Heap exhausted, game over. Welcome to LDB, a low-level debugger for the Lisp runtime environment. ldb> Error opening /dev/tty: No such device or address
m
Your message was too long, here's your message at the allowed limit: lots of warnings on columns remain but also more severe looking stuff 2025-11-19T23 26 20 306892Z ERROR Database error 23505 duplicate key value violates unique constraint pk_flow DETAIL Key (id)=(1e44683c-a3b2-452b-925c-da74daebd1e2) already exists CONTEXT COPY flow line 1 2025-11-19T23 26 20 364549Z ERROR Database error 23505 duplicate key value violates unique constraint pk_concurrency_limit DETAIL Key (id)=(0e3c7b58-c543-4aba-87df-b386d09ba8b6) already exists CONTEXT COPY concurrency_limit line 1 2025-11-19T23 26 20 599021Z ERROR Database error 23505 duplicate key value violates unique constraint pk_block_schema_reference DETAIL Key (id)=(2d6c80e4-870b-41a0-bd14-d423e5c2cd1e) already exists CONTEXT COPY block_schema_reference line 1 2025-11-19T23 26 20 606709Z ERROR Database error 23505 duplicate key value violates unique constraint uq_block_schema__checksum_version DETAIL Key (checksum version)=(sha256 61e9dbea14935ccb2cfac7eb38f01c4e878a81073806f2aec993820fa0d91eb3 3 4 17) already exists CONTEXT COPY block_schema line 1 2025-11-19T23 26 20 748930Z ERROR Database error 23505 duplicate key value violates unique constraint pk_block_document_reference DETAIL Key (id)=(3f615161-685e-4f14-af19-5c8658d80ccd) already exists CONTEXT COPY block_document_reference line 1 2025-11-19T23 26 20 748930Z ERROR Database error 23505 duplicate key value violates unique constraint pk_block_document DETAIL Key (id)=(e4c96407-768c-4b88-acbd-2265f448c5f3) already exists CONTEXT COPY block_document line 1 2025-11-19T23 26 20 806587Z ERROR Database error 23505 duplicate key value violates unique constraint pk_worker DETAIL Key (id)=(47b18cd5-76c4-42a5-a718-94e3530d026b) already exists CONTEXT COPY worker line 1 2025-11-19T23 26 20 814274Z ERROR Database error 23505 duplicate key value violates unique constraint uq_block_type__slug DETAIL Key (slug)=(webhook) already exists CONTEXT COPY block_type line 1 2025-11-19T23 26 21 075652Z ERROR Database error 23505 duplicate key value violates unique constraint pk_flow_run_state DETAIL Key (id)=(01993eaf-d045-77b0-b695-1ee3e0b4640f) already exists CONTEXT COPY flow_run_state line 1 2025-11-19T23 26 21 164059Z ERROR Database error 23505 duplicate key value violates unique constraint uq_work_pool__name DETAIL Key (name)=(production-pool) already exists CONTEXT COPY work_pool line 1 2025-11-19T23 26 25 556442Z ERROR Database error 23505 duplicate key value violates unique constraint pk_log DETAIL Key (id)=(01993eaf-ec3b-7d7d-b153-47a9f5cc9cf7) already exists CONTEXT COPY log line 1 2025-11-19T23 26 30 599636Z ERROR Database error 23505 duplicate key value violates unique constraint pk_task_run_state DETAIL Key (id)=(019956d5-40f5-7bb8-a0ac-a79f661c9969) already exists CONTEXT COPY task_run_state line 1 2025-11-19T23 26 30 645800Z ERROR Database error 23505 duplicate key value violates unique constraint pk_work_queue DETAIL Key (id)=(09012307-9413-41d3-90c0-33005637bd37) already exists CONTEXT COPY work_queue line 1 Heap exhausted during garbage collection 1456 bytes available 4496 requested Gen Boxed Code Raw LgBox LgCode LgRaw Pin Alloc Waste Trig WP GCs Mem-age 0 1715 0 1026 0 0 0 5 86492304 3324784 43467962 2741 1 0 0000 1 97 0 6544 0 0 13496 13460 651964464 7884752 10737418 20137 0 1 1470 2 250 0 6076 0 0 0 90 202151936 5138432 2000000 6326 0 0 6800 3 0 0 0 0 0 0 0 0 0 2000000 0 0 0 0000 4 0 0 0 0 0 0 0 0 0 2000000 0 0 0 0000 5 0 0 0 0 0 0 0 0 0 2000000 0 0 0 0000 6 1734 7 1426 287 0 110 0 114478960 2306192 2000000 3564 0 0 0000 Total bytes allocated = 1055087664 Dynamic-space-size bytes = 1073741824 GC control variables GC-INHIBIT = true GC-PENDING = true STOP-FOR-GC-PENDING = false fatal error encountered in SBCL pid 1 tid
✅ thought for 59.6 seconds
Those duplicate key errors mean rows already exist in your Postgres tables (likely from a partial pgloader run and/or because the server/work-pool creator connected and seeded data). The cleanest fix is: - ensure the target DB has the correct schema but is empty - then run pgloader in data-only mode - exclude SQLite internals and alembic_version - optionally reduce batch sizes to avoid the pgloader heap OOM Do this step-by-step: 1) Stop anything that writes to Prefect - Stop your Prefect server, workers, and any “create-work-pools”/init jobs so nothing touches Postgres during migration. 2) Bring up Postgres (so the network exists)
Copy code
docker compose up -d postgres
3) Reset the Postgres schema (wipes any partial data) - This drops and recreates all tables. It will also seed some defaults, which we’ll truncate before the load.
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database reset -y
Optional: Re-run upgrade just to be explicit.
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database upgrade -y
4) Create a stronger pgloader config - Replace pgloader.load with this. It: - TRUNCATEs all public tables except alembic_version (so the DB is empty), - excludes SQLite internal tables and alembic_version, - uses smaller batch sizes to avoid the Lisp heap OOM you hit.
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      batch rows = 1000,
      prefetch rows = 1000,
      on error stop

 BEFORE LOAD DO
  $$
  DO $do$
  DECLARE r record;
  BEGIN
    FOR r IN
      SELECT tablename
      FROM pg_tables
      WHERE schemaname = 'public'
        AND tablename <> 'alembic_version'
    LOOP
      EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
  END;
  $do$;
  $$

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
5) Run pgloader with the volume mounts
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Tip: If you still see heap exhausted, either: - lower batch/prefetch further (e.g., 500), or - give the container more memory: 
docker run --memory=4g ...
6) Sanity checks
Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
7) Start Prefect against Postgres (and keep your event settings) - In compose: - Remove angle brackets from the URL. - Pin the image to 3.4.17 for now. - Keep: - 
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
- 
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
- 
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
- 
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S
- Bring up the server, then workers. Avoid running your “create-work-pools” init until after migration completes (it can re-create resources you already migrated). Why this fixes the errors you saw - The varchar/text warnings are harmless. - 
sqlite_stat1
is excluded now. - Duplicates (flow, logs, block types, work pools, etc) happen if the target already has rows — reset + TRUNCATE ensures the target is empty before loading. - We keep Postgres’s alembic_version intact so future migrations work.
If you hit any new constraint errors - Paste the exact table/constraint from pgloader. I’ll update the load file to either: - exclude a table you should keep from Postgres (rare), or - add a TRUNCATE/ordering tweak for that table. Once you’re up on Postgres, the UI slowness and memory spikes should drop significantly, especially with your shorter events backfill/retention.
o
@Marvin erver database reset -y Downgrading database... Traceback (most recent call last): File "/usr/local/lib/python3.12/site-packages/prefect/cli/_utilities.py", line 44, in wrapper return fn(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/cli/_types.py", line 156, in sync_fn return asyncio.run(async_fn(*args, **kwargs)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/asyncio/runners.py", line 195, in run return runner.run(main) ^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/asyncio/runners.py", line 118, in run return self._loop.run_until_complete(task) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/asyncio/base_events.py", line 691, in run_until_complete return future.result() ^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/cli/server.py", line 415, in reset await db.drop_db() File "/usr/local/lib/python3.12/site-packages/prefect/server/database/interface.py", line 81, in drop_db await self.run_migrations_downgrade(revision="base") File "/usr/local/lib/python3.12/site-packages/prefect/server/database/interface.py", line 89, in run_migrations_downgrade await run_sync_in_worker_thread(alembic_downgrade, revision=revision) File "/usr/local/lib/python3.12/site-packages/prefect/utilities/asyncutils.py", line 233, in run_sync_in_worker_thread result = await anyio.to_thread.run_sync( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/anyio/to_thread.py", line 56, in run_sync return await get_async_backend().run_sync_in_worker_thread( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 2476, in run_sync_in_worker_thread return await future ^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 967, in run result = context.run(func, *args) ^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/utilities/asyncutils.py", line 243, in call_with_mark return call() ^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/server/database/alembic_commands.py", line 36, in wrapper return fn(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/server/database/alembic_commands.py", line 87, in alembic_downgrade alembic.command.downgrade(alembic_config(), revision, sql=dry_run) File "/usr/local/lib/python3.12/site-packages/alembic/command.py", line 530, in downgrade script.run_env() File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 549, in run_env util.load_python_file(self.dir, "env.py") File "/usr/local/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file module = load_module_py(module_id, path) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 136, in load_module_py spec.loader.exec_module(module) # type: ignore ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "frozen importlib._bootstrap_external", line 999, in exec_module File "frozen importlib._bootstrap", line 488, in _call_with_frames_removed File "/usr/local/lib/python3.12/site-packages/prefect/server/database/_migrations/env.py", line 201, in module run_async_from_worker_thread(apply_migrations) File "/usr/local/lib/python3.12/site-packages/prefect/utilities/asyncutils.py", line 254, in run_async_from_worker_thread return anyio.from_thread.run(call) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/anyio/from_thread.py", line 59, in run return async_backend.run_async_from_thread(func, args, token=token) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 2516, in run_async_from_thread return f.result() ^^^^^^^^^^ File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 456, in result return self.__get_result() ^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 401, in __get_result raise self._exception File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 2503, in task_wrapper return await func(*args) ^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/server/database/_migrations/env.py", line 190, in apply_migrations await connection.run_sync(do_run_migrations) File "/usr/local/lib/python3.12/site-packages/sqlalchemy/ext/asyncio/engine.py", line 887, in run_sync return await greenlet_spawn( ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 203, in greenlet_spawn result = context.switch(value) ^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/server/database/_migrations/env.py", line 159, in do_run_migrations context.run_migrations() File "string", line 8, in run_migrations File "/usr/local/lib/python3.12/site-packages/alembic/runtime/environment.py", line 946, in run_migrations self.get_context().run_migrations(**kw) File "/usr/local/lib/python3.12/site-packages/alembic/runtime/migration.py", line 615, in run_migrations for step in self._migrations_fn(heads, self): ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/alembic/command.py", line 519, in downgrade return script._downgrade_revs(revision, rev) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 431, in _downgrade_revs with self._catch_revision_errors( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/contextlib.py", line 158, in exit self.gen.throw(value) File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 249, in _catch_revision_errors raise util.CommandError(resolution) from re alembic.util.exc.CommandError: Can't locate revision identified by '8bb517bae6f9' An exception occurred.
m
Your message was too long, here's your message at the allowed limit: erver database reset -y Downgrading database Traceback (most recent call last) File /usr/local/lib/python3 12/site-packages/alembic/script/base py line 217 in _catch_revision_errors yield File /usr/local/lib/python3 12/site-packages/alembic/script/base py line 443 in _downgrade_revs for script in revs ^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 814 in iterate_revisions revisions heads = fn( ^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 1391 in _collect_downgrade_revisions heads = self get_revisions(upper) ^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 542 in get_revisions return sum([self get_revisions(id_elem) for id_elem in id_] ()) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 565 in get_revisions return tuple( ^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 566 in <genexpr> self _revision_for_ident(rev_id branch_label) File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 637 in _revision_for_ident raise ResolutionError( alembic script revision ResolutionError No such revision or branch '8bb517bae6f9' The above exception was the direct cause of the following exception Traceback (most recent call last) File /usr/local/lib/python3 12/site-packages/prefect/cli/_utilities py line 44 in wrapper return fn(*args *kwargs) ^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/cli/_types py line 156 in sync_fn return asyncio run(async_fn(*args *kwargs)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/asyncio/runners py line 195 in run return runner run(main) ^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/asyncio/runners py line 118 in run return self _loop run_until_complete(task) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/asyncio/base_events py line 691 in run_until_complete return future result() ^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/cli/server py line 415 in reset await db drop_db() File /usr/local/lib/python3 12/site-packages/prefect/server/database/interface py line 81 in drop_db await self run_migrations_downgrade(revision= base ) File /usr/local/lib/python3 12/site-packages/prefect/server/database/interface py line 89 in run_migrations_downgrade await run_sync_in_worker_thread(alembic_downgrade revision=revision) File /usr/local/lib/python3 12/site-packages/prefect/utilities/asyncutils py line 233 in run_sync_in_worker_thread result = await <http //anyio to|anyio to>_thread run_sync( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/to_thread py line 56 in run_sync return await get_async_backend() run_sync_in_worker_thread( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 2476 in run_sync_in_worker_thread return await future ^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 967 in run result = context run(func *args) ^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/utilities/asyncutils py line 243 in call_with_mark return call() ^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/server/database/alembic_commands py line 36 in wrapper return fn(*args **kwargs) ^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/server/database/alembic_commands py line 87 in alembic_downgrade alembic command downgrade(alembic_config() revision sql=dry_run) File /usr/local/lib/python3 12/site-packages/alembic/command py line 530 in downgrade script run_env() File /usr/local/lib/python3 12/site-packages/alembic/script/base py line 549 in run_env util load_python_file(self dir env py ) File /usr/local/lib/python3 12/site-packa
ges/alembic/util/pyfiles py line 116 in load_python_file module = load_module_py(module_id path) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/util/pyfiles py line 136 in load_module_py spec loader exec_module(module) # type ignore ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File <frozen importlib _bootstrap_external> line 999 in exec_module File <frozen importlib _bootstrap> line 488 in _call_with_frames_removed File /usr/local/lib/python3 12/site-packages/prefect/server/database/_migrations/env py line 201 in <module> run_async_from_worker_thread(apply_migrations) File /usr/local/lib/python3 12/site-packages/prefect/utilities/asyncutils py line 254 in run_async_from_worker_thread return anyio from_thread run(call) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/from_thread py line 59 in run return async_backend run_async_from_thread(func args token=token) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 2516 in run_async_from_thread return f result() ^^^^^^^^^^ File /usr/local/lib/python3 12/concurrent/futures/_base py line 456 in result return self __get_result() ^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/concurrent/futures/_base py line 401 in __get_result raise self _exception File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 2503 in task_wrapper return await func(*args) ^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/server/database/_migrations/env py line 190 in apply_migrations await connection run_sync(do_run_migrations) File /usr/local/lib/python3 12/site-packages/sqlalchemy/ext/asyncio/engine py line 887 in run_sync return await greenlet_spawn( ^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/sqlalchemy/util/_concurrency_py3k
✅ thought for 118.5 seconds
Thanks — that traceback explains it. The reset failed because your Postgres DB’s 
alembic_version
contains a SQLite migration revision (
8bb517bae6f9
). Alembic can’t find that revision in the Postgres migration chain, so 
reset
can’t downgrade to base. Two ways to recover — pick the one you prefer: Option A: Stamp to a valid Postgres revision, then reset 1) Stamp the DB to the current Postgres head (this only fixes the revision pointer; it won’t touch data):
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database stamp head
2) Now reset (downgrade to base then upgrade back to head):
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database reset -y
3) Run the pgloader data-only import with a TRUNCATE of all tables except alembic_version to avoid duplicates: Create/replace pgloader.load:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      batch rows = 1000,
      prefetch rows = 1000,
      on error stop

 BEFORE LOAD DO
  $$
  DO $do$
  DECLARE r record;
  BEGIN
    FOR r IN
      SELECT tablename
      FROM pg_tables
      WHERE schemaname = 'public'
        AND tablename <> 'alembic_version'
    LOOP
      EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
  END;
  $do$;
  $$

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
Run it:
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Option B: Nuke and recreate the Postgres DB (clean slate) - Stop Postgres and delete the data volume (fastest if this DB is only for Prefect): - Find the volume name: 
docker volume ls
(likely 
qfin_postgres_data
) - Remove it: 
docker volume rm qfin_postgres_data
- Bring Postgres back up:
Copy code
docker compose up -d postgres
- Create schema:
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database upgrade -y
- Then run the same pgloader command above (the TRUNCATE block is harmless on empty tables). Notes - Keep Prefect server/workers/any init jobs stopped during migration to avoid new writes and duplicates. - If pgloader runs out of heap again, lower `batch rows`/`prefetch rows` to 500 or add a memory limit override like 
--memory=4g
. - After pgloader completes, you can run a no-op sync:
Copy code
docker run --rm --network qfin_default \
  -e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
  prefecthq/prefect:3.4.17 prefect server database upgrade -y
- In your compose, pin the server to 
prefecthq/prefect:3.4.17
, remove angle brackets in env vars, and keep your event settings: - 
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
- 
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
- 
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
- 
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S
Sanity checks after load
Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
If you hit any new duplicate/constraint messages from pgloader, paste the exact table/constraint and I’ll tweak the load file further (e.g., exclude or pre-truncate specific seeded tables).
o
@Marvin both option a and b (nuke) fails with ✔️ Container qfin-postgres-1 Running 0.0s olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker run --rm --network qfin_default -e PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://prefect:prefect@postgres:5432/prefect prefecthq/prefect:3-latest prefect server database upgrade -y Running upgrade migrations ... Traceback (most recent call last): File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 217, in _catch_revision_errors yield also there is no 3.4.17 locally or on hub so i use latest both in compose file and in my commands
m
✅ thought for 49.5 seconds
Thanks for sticking with this — a couple things to fix before we try again: Key issues I see - You’re still passing angle brackets in the env var: 
PREFECT_API_DATABASE_CONNECTION_URL=<...>
. Those must be removed — the URL should be a plain string. - Using the same image tag for migration and for your server avoids revision mismatches. If you’re going to run the server as 
prefecthq/prefect:3-latest
, use that for the migration too. Let’s do a clean, predictable migration using docker compose run so we inherit the same env as your server. 1) Fix your compose env and pin the tag you intend to run - In your docker-compose.yml, change these (remove angle brackets; keep your event settings):
Copy code
services:
  prefect-server:
    image: prefecthq/prefect:3-latest
    environment:
      PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
      PREFECT_API_DATABASE_MIGRATE_ON_START: "false"   # we will run migrations manually
      PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D"
      PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S"
      PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100"
      PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S"
    command: prefect server start --host 0.0.0.0
2) Nuke only the Postgres data volume to start fresh - Stop everything and remove the Postgres data volume:
Copy code
docker compose down
docker volume rm qfin_postgres_data
If your volume name is just postgres_data under this project, Docker will have named it qfin_postgres_data. You can confirm with:
Copy code
docker volume ls | grep qfin
3) Bring Postgres up, then create schema via compose-run
Copy code
docker compose up -d postgres
# optional but recommended
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
# run migrations using the same image/env as your server service
docker compose run --rm prefect-server prefect server database upgrade -y
Using compose run ensures the connection URL/env is exactly the same as your service. If this fails, please paste the FULL traceback so I can pinpoint it. 4) Use pgloader with a data-only load and TRUNCATE - Create pgloader.load in your current dir:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      batch rows = 1000,
      prefetch rows = 1000,
      on error stop

 BEFORE LOAD DO
  $$
  DO $do$
  DECLARE r record;
  BEGIN
    FOR r IN
      SELECT tablename
      FROM pg_tables
      WHERE schemaname = 'public'
        AND tablename <> 'alembic_version'
    LOOP
      EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
  END;
  $do$;
  $$

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
- Run pgloader on the compose network with your SQLite file mounted:
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
If it OOMs again, reduce batch/prefetch to 500 or add memory: 
--memory=4g
. 5) Sanity checks
Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
6) Start Prefect and workers
Copy code
docker compose up -d prefect-server
# once healthy, start workers
docker compose up -d prefect-prod-worker prefect-dev-worker
If “upgrade migrations” still errors - It’s almost always one of: - URL still malformed (angle brackets or quoting issues) - DB not reachable (network mismatch) - Postgres extension missing (pg_trgm)
- A stale alembic_version (should not happen after volume nuke) - Please paste the FULL traceback (from “Running upgrade migrations ...” down) and I’ll diagnose exactly. Useful docs - Server CLI (database): Self-hosted Server CLI - DB settings: Settings reference — database - Events settings: Settings reference — server events Once we get through the “upgrade” step cleanly, the pgloader run with TRUNCATE will avoid all the duplicate key errors you hit earlier, and your UI/memory situation should improve significantly.
o
@Marvin olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker compose run --rm prefect-server prefect server database upgrade -y WARN[0000] volume "brrr_prefect_data" already exists but was created for project "brrr" (expected "qfin"). Use 
external: true
to use an existing volume [+] Creating 1/1 ✔️ Container qfin-postgres-1 Running 0.0s Running upgrade migrations ... Migrations succeeded! Prefect database at postgresql+asyncpg://prefect:***@postgres:5432/prefect upgraded! got this warning on #3. Is that an issue? brrr_prefect_data is volume after my sqlite implementation and is still listed under volumes in docker compose volumes: prefect_data: name: brrr_prefect_data postgres_data: also references under volumes in prefect-server: volumes: # persist Prefect's local data/db - prefect_data:/root/.prefect
m
✅ thought for 48.4 seconds
Nice — migrations succeeded, that’s the big hurdle. About the warning: - “volume brrr_prefect_data already exists but was created for project brrr (expected qfin)” just means your Compose project name changed but you’re reusing a volume created under the old project. - Functionally it’s fine. To silence it, declare the volume as external and keep the explicit name. Update your volumes section like this:
Copy code
volumes:
  prefect_data:
    external: true
    name: brrr_prefect_data
  postgres_data:
And keep the service mount as-is:
Copy code
services:
  prefect-server:
    ...
    volumes:
      - prefect_data:/root/.prefect
Notes: - For a Postgres-backed server, mounting 
~/.prefect
is not required anymore. I’d keep it mounted until you finish the pgloader copy, then remove the mount (and delete the volume after you’ve backed up the SQLite file), to avoid confusion. Finish the migration 1) Create/confirm the pgloader config file (data-only, exclude alembic_version and sqlite internals, TRUNCATE target tables):
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      batch rows = 1000,
      prefetch rows = 1000,
      on error stop

 BEFORE LOAD DO
  $$
  DO $do$
  DECLARE r record;
  BEGIN
    FOR r IN
      SELECT tablename
      FROM pg_tables
      WHERE schemaname = 'public'
        AND tablename <> 'alembic_version'
    LOOP
      EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
  END;
  $do$;
  $$

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
2) Run pgloader on the compose network:
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
- If it runs out of memory, drop batch/prefetch to 500 or add 
--memory=4g
. 3) Sanity check a few counts:
Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
4) Start Prefect against Postgres (keep your event settings), then start workers. Optional cleanup after verification - Backup the SQLite file from the old volume:
Copy code
docker run --rm -v brrr_prefect_data:/data -v $(pwd):/backup alpine sh -c "cp /data/prefect.db /backup/prefect.db.backup.sqlite"
- Remove the mount from 
prefect-server
and, when you’re comfortable, remove the old volume:
Copy code
docker volume rm brrr_prefect_data
If pgloader throws any new duplicate/constraint errors, paste the exact lines and I’ll tweak the load file further.
o
@Marvin some ugly errors remain on pgloader: 2025-11-19T235313.708873Z ERROR Error while formatting a row from "flow_run": 2025-11-19T235313.708873Z ERROR A SB-KERNEL::HEAP-EXHAUSTED-ERROR condition without bindings for heap statistics. (If you did not expect to see this message, please report it. 2025-11-19T235313.724105Z ERROR Database error 22007: invalid input syntax for type interval: "1970-01-01 000000.000000" CONTEXT: COPY flow_run, line 1, column total_run_time: "1970-01-01 000000.000000" Heap exhausted during allocation: 373587968 bytes available, 499842272 requested. 2025-11-19T235325.489317Z ERROR Database error 22007: invalid input syntax for type interval: "1970-01-01 000000.082295" CONTEXT: COPY task_run, line 1, column total_run_time: "1970-01-01 000000.082295" 2025-11-19T235328.651281Z ERROR PostgreSQL Database error 23503: insert or update on table "flow_run_state" violates foreign key constraint "fk_flow_run_state__flow_run_id__flow_run" DETAIL: Key (flow_run_id)=(6bd95e67-8a67-4607-afff-975415b1cf34) is not present in table "flow_run" 2025-11-19T235328.666501Z ERROR PostgreSQL Database error 23503: insert or update on table "task_run_state" violates foreign key constraint "fk_task_run_state__task_run_id__task_run" DETAIL: Key (task_run_id)=(019956d5-40f5-727a-98e9-46d436b79e67) is not present in table "task_run". QUERY: ALTER TABLE "public"."task_run_state" ADD CONSTRAINT "fk_task_run_state__task_run_id__task_run" FOREIGN KEY (task_run_id) REFERENCES task_run(id) ON DELETE CASCADE
m
✅ thought for 97.7 seconds
Good finds — those errors tell us two things: - The interval columns (
flow_run.total_run_time
, 
task_run.total_run_time
) are stored as epoch-based timestamps in SQLite (e.g., “1970-01-01 000000.082295”) but are INTERVAL in Postgres. We need to transform them. - The FK errors on state tables happened because some parent rows failed to load earlier (due to the interval errors). Fixing the interval transform should unblock FK creation. Let’s make pgloader do a text-then-convert approach so COPY succeeds, then we normalize to interval in Postgres. 1) Use this pgloader.load - It truncates all target tables (except alembic_version), excludes SQLite internals, loads everything as-is, but first makes those columns TEXT; then after load converts the values from “YYYY-MM-DD HHMMSS.US” to “HHMMSS.US” and casts back to INTERVAL. Create/replace pgloader.load with:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      batch rows = 500,
      prefetch rows = 500,
      on error stop

 BEFORE LOAD DO
  $$
  -- 1) Empty all public tables, keep alembic_version
  DO $do$
  DECLARE r record;
  BEGIN
    FOR r IN
      SELECT tablename
      FROM pg_tables
      WHERE schemaname = 'public'
        AND tablename <> 'alembic_version'
    LOOP
      EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
  END;
  $do$;
  -- 2) Temporarily make total_run_time columns TEXT so COPY won't fail
  ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text;
  ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text;
  $$

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'

 AFTER LOAD DO
  $$
  -- 3) Normalize text values: strip the date part, keep only time part 'HH:MM:<http://SS.US|SS.US>'
  UPDATE flow_run
    SET total_run_time =
      CASE
        WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
        WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
        ELSE total_run_time
      END;

  UPDATE task_run
    SET total_run_time =
      CASE
        WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
        WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
        ELSE total_run_time
      END;

  -- 4) Cast back to INTERVAL (Postgres accepts 'HH:MM:<http://SS.US|SS.US>' as an interval literal)
  ALTER TABLE flow_run
    ALTER COLUMN total_run_time TYPE interval
    USING NULLIF(total_run_time, '')::interval;

  ALTER TABLE task_run
    ALTER COLUMN total_run_time TYPE interval
    USING NULLIF(total_run_time, '')::interval;
  $$
;
2) Run it with smaller batches (and consider more memory)
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
- If you still see heap exhausted from pgloader, either: - reduce to 
batch rows = 200
and 
prefetch rows = 200
, or - add memory to the container: 
docker run --memory=4g ...
, and/or - increase Docker Desktop/WSL memory in settings. 3) Why this resolves your errors - Interval errors: COPY now inserts the SQLite value as TEXT; we then normalize to a time-like string and cast to INTERVAL after the load. - Foreign key errors: those were a knock‑on effect of failed parent inserts; once flow_run/task_run insert cleanly, FK creation on state tables should succeed when pgloader reapplies constraints/indexes at the end. 4) Optional: reduce load size further - If your logs/events are huge and not essential historically, you can temporarily exclude them to get unblocked, then circle back: - Add to the load file: - 
EXCLUDING TABLE NAMES LIKE 'events'
- 
EXCLUDING TABLE NAMES LIKE 'log'
- Or migrate those two tables separately later. 5) After load: sanity checks ```
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run_state;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run_state;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
Copy code
6) Compose volume notice
- Your warning about the old volume is fine; declare it as external to silence:
volumes: prefect_data: external: true name: brrr_prefect_data postgres_data: ``` - Keep it mounted through the migration. After verification, you can remove that mount (Postgres is your source of truth now) and archive/remove the old SQLite volume whenever you’re ready. If you hit any new errors (especially on AFTER LOAD), paste the exact lines and I’ll tweak the statements. If pgloader continues to struggle with memory on logs/events, we can exclude those tables for now to complete the migration, then either re‑ingest a recent slice or let your retention settings rebuild from fresh data.
o
@Marvin docker run --rm --network qfin_default \ -v brrr_prefect_data:/root/.prefect \ -v $(pwd)/pgloader.load:/load.load \ dimitri/pgloader:latest pgloader /load.load 2025-11-20T000054.030426Z LOG pgloader version "3.6.7~devel" 2025-11-20T000054.038033Z LOG Parsing commands from file #P"/load.load" KABOOM! COLUMNS-ERROR: Database error 42601: cannot insert multiple commands into a prepared statement QUERY: DO $do$ DECLARE r record; BEGIN FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename <> 'alembic_version' LOOP EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE'; END LOOP; END; $do$; -- 2) Temporarily make total_run_time columns TEXT so COPY won't fail ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text; ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text; An unhandled error condition has been signalled: Database error 42601: cannot insert multiple commands into a prepared statement
m
✅ thought for 71.4 seconds
Great catch — that pgloader error is because it tried to send multiple SQL statements as a single prepared statement. We can fix this by: - letting pgloader handle truncation automatically, and - splitting each ALTER/UPDATE into its own BEFORE/AFTER clause. Also, we’ll transform the total_run_time columns to text before the load, then normalize and cast back to interval after the load. Here’s a working load file. Try this pgloader.load - Note: we keep batch/prefetch small to avoid heap OOM. You can tune up once it works. - If your logs/events are huge, I’ve added an optional “exclude” variant below.
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,              -- pgloader will TRUNCATE each target table before copying
      batch rows = 500,
      prefetch rows = 500,
      on error stop

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'

 -- Temporarily change interval columns to text so COPY won’t fail on SQLite-formatted values
 BEFORE LOAD DO $$ ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text; $$
 BEFORE LOAD DO $$ ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text; $$

 -- Normalize the text values to time-only strings
 AFTER LOAD DO $$
   UPDATE flow_run
     SET total_run_time =
       CASE
         WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
         WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
         ELSE total_run_time
       END;
 $$

 AFTER LOAD DO $$
   UPDATE task_run
     SET total_run_time =
       CASE
         WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
         WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
         ELSE total_run_time
       END;
 $$

 -- Cast back to INTERVAL
 AFTER LOAD DO $$
   ALTER TABLE flow_run
     ALTER COLUMN total_run_time TYPE interval
     USING NULLIF(total_run_time, '')::interval;
 $$

 AFTER LOAD DO $$
   ALTER TABLE task_run
     ALTER COLUMN total_run_time TYPE interval
     USING NULLIF(total_run_time, '')::interval;
 $$
;
Run it
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
- If pgloader’s heap still exhausts, either lower to 200/200 or give it more memory:
Copy code
docker run --rm --memory=4g --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Optional: exclude logs and events on the first pass (helps with memory) - Add these two lines to the EXCLUDING section to unblock the core entities:
Copy code
EXCLUDING TABLE NAMES LIKE 'log'
EXCLUDING TABLE NAMES LIKE 'events'
- You can run a second pgloader job later just for those tables (or decide to keep fresh logs/events given you’ve set short retention). Why this fixes the errors - “cannot insert multiple commands into a prepared statement” is resolved by giving pgloader one statement per BEFORE/AFTER clause. - Interval parse errors are resolved by temporarily making those columns text, normalizing their values, and casting back to interval after load. - FK errors on state tables were fallout from failed parent inserts; once the parent tables load cleanly, FK creation should succeed. Sanity-check after load ``` docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run_state;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run_state;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;" ``` One more reminder - Make sure your compose uses a plain URL (no angle brackets) and you’re using the same image tag for server and CLI (you’re on 3-latest now, which is fine as long as both use it). Keep your event settings to reduce UI load: - 
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
- 
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
- 
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
- 
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S
If this run surfaces any new specific table/constraint errors, paste those lines and I’ll adjust the load file accordingly.
o
@Marvin i set rows to 200, still Heap exhausted during allocation: 6553600 bytes available, 110034048 requested. 2025-11-20T000404.239052Z ERROR Error while formatting a row from "flow_run": 2025-11-20T000404.239052Z ERROR A SB-KERNEL::HEAP-EXHAUSTED-ERROR condition without bindings for heap statistics. (If you did not expect to see this message, please report it. 2025-11-20T000404.246653Z ERROR The value NIL is not of type NUMBER when binding SB-KERNEL::X 2025-11-20T000404.246653Z WARNING PostgreSQL warning: there is no transaction in progress Heap exhausted during allocation: 6553600 bytes available, 499842272 requested. fatal error encountered in SBCL pid 1 tid 12: Heap exhausted, game over.
m
✅ thought for 124.9 seconds
You’ve hit two separate pgloader pain points: - SBCL heap exhaustion inside the pgloader container - Type/constraint mismatches that we’ve been working around At this point, the fastest path is to get you unblocked by reducing what pgloader has to chew on, then decide how much history you want to bring over. You have three viable paths: Option 1 — Keep pgloader, but make it lighter - Exclude the heaviest tables (log, events) for now, finish the core migration, then decide if you want to backfill a recent slice of logs/events. - Bump container memory and keep batches small. Use this load file to migrate everything except logs/events:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
 EXCLUDING TABLE NAMES LIKE 'log'
 EXCLUDING TABLE NAMES LIKE 'events'

 BEFORE LOAD DO $$ ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text; $$
 BEFORE LOAD DO $$ ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text; $$

 AFTER LOAD DO $$
   UPDATE flow_run
     SET total_run_time =
       CASE
         WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
         WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
         ELSE total_run_time
       END;
 $$
 AFTER LOAD DO $$
   UPDATE task_run
     SET total_run_time =
       CASE
         WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
         WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
         ELSE total_run_time
       END;
 $$
 AFTER LOAD DO $$
   ALTER TABLE flow_run
     ALTER COLUMN total_run_time TYPE interval
     USING NULLIF(total_run_time, '')::interval;
 $$
 AFTER LOAD DO $$
   ALTER TABLE task_run
     ALTER COLUMN total_run_time TYPE interval
     USING NULLIF(total_run_time, '')::interval;
 $$
;
Run it with extra memory (and keep your compose network):
Copy code
docker run --rm --memory=6g --memory-swap=6g --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
After this, you’ll have flows, deployments, work pools/queues, blocks, runs/states, etc. Logs/events will start fresh (and you’ve already shortened retention, which helps UI performance). If you later want recent logs/events, we can import just the last N days via CSV without stressing pgloader. Option 2 — Migrate only metadata (easiest, smallest) If you primarily care about deployments, blocks, work pools/queues, variables, and don’t need historical runs/logs/events, do a metadata-only copy. This is tiny and very reliable. Example load file:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 500,
      prefetch rows = 500,
      on error stop

 INCLUDING ONLY TABLE NAMES MATCHING
   'block_type', 'block_schema', 'block_schema_reference',
   'block_document', 'block_document_reference',
   'deployment', 'deployment_version', 'flow', 'flow_tag',
   'work_pool', 'work_pool_queue', 'worker', 'concurrency_limit',
   'variable', 'automation%', 'artifact%'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
This preserves your deploys/blocks/pools so you can keep working immediately. Historical runs/logs/events will start fresh (usually fine given your new retention and that UI speed is your priority). Option 3 — Skip pgloader for heavy tables; import recent slices via CSV If you want, say, the last 3–7 days of logs/events: - Export from SQLite with a WHERE filter (e.g., timestamp >= now - 7 days)
- Import into Postgres using psql’s \copy into staging tables, then INSERT … ON CONFLICT DO NOTHING into the real tables - We can share exact commands tailored to your schema version, but this is more steps; happy to write them if you want this route My recommendation - Given the repeated SBCL heap crashes, go with Option 1: exclude log + events for now, finish the rest, and revisit logs/events later if truly needed. This will get you running on Postgres quickly and will materially improve UI load and RAM usage. - Also increase Docker Desktop/WSL memory allocation generally to help pgloader; the SBCL heap exhaustion is a known annoyance in resource-constrained environments. Quick confirmations - Your compose is now using a plain URL (no < >) and both the server and the CLI use the same image tag (3-latest). Good. - The “brrr_prefect_data was created for project brrr” warning is harmless; set the volume to external to silence it:
Copy code
volumes:
  prefect_data:
    external: true
    name: brrr_prefect_data
  postgres_data:
If you want me to: - prepare a “metadata-only” pgloader file that exactly matches your DB (or) - craft CSV-based commands to import a recent slice of logs/events …just tell me which path you prefer and the time window you want for history (e.g., last 3/7/14 days).
o
@Marvin i tried "1" and it seems to have freezed. stuck for 10min + at WARNING Source column "public"."task_run"."labels" is casted to type "json" which is not the same as "jsonb", the type of current target database column "public"."task_run"."labels".
m
✅ thought for 54.2 seconds
Totally get it — pgloader can look “stuck” on those type warnings and it’s touchy on memory. Two tracks to unblock you quickly: First, verify if it’s actually stuck or just busy - Check copy/index progress in Postgres:
Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), phase, relid::regclass, bytes_processed, tuples_processed FROM pg_stat_progress_copy;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c \"SELECT state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';\"
If you see rows in these, it’s working; if not, it’s likely stalled on memory/transform. If it’s stalling, here are the two most reliable paths: Option A — Get you running now: migrate only metadata (no runs/logs/events) This is the smallest copy, very reliable, and gets your server back up quickly. You keep deployments, blocks, work pools/queues, variables, automations, artifacts, etc. Historical runs/logs/events start fresh (usually fine given you want the UI fast and have shorter retention). Use this pgloader.load:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 INCLUDING ONLY TABLE NAMES MATCHING
   'block_type', 'block_schema', 'block_schema_reference',
   'block_document', 'block_document_reference',
   'deployment', 'deployment_version', 'flow', 'flow_tag',
   'work_pool', 'work_pool_queue', 'worker', 'concurrency_limit',
   'variable', 'automation%', 'artifact%'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
Run:
Copy code
docker run --rm --network qfin_default \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  --memory=6g --memory-swap=6g \
  dimitri/pgloader:latest pgloader /load.load
Then bring up Prefect against Postgres (keep your event settings). You’ll be up and stable with good UI performance. If you want, we can later import a small recent slice of runs via CSV. Option B — Bring over recent runs without crushing memory (CSV, targeted) If you want the last N days of flow/task runs too, we can export a filtered CSV from SQLite and import into Postgres. Example for 7 days: 1) Export recent runs from SQLite to CSV
Copy code
docker run --rm -v brrr_prefect_data:/data alpine sh -lc '
apk add --no-cache sqlite;
sqlite3 -csv /data/prefect.db "
.headers on
.mode csv
.output /data/flow_run_7d.csv
SELECT * FROM flow_run WHERE start_time >= datetime(\"now\",\"-7 day\");
.output /data/task_run_7d.csv
SELECT * FROM task_run WHERE start_time >= datetime(\"now\",\"-7 day\");
.output /data/flow_run_state_7d.csv
SELECT s.* FROM flow_run_state s JOIN flow_run r ON r.id=s.flow_run_id WHERE r.start_time >= datetime(\"now\",\"-7 day\");
.output /data/task_run_state_7d.csv
SELECT s.* FROM task_run_state s JOIN task_run r ON r.id=s.task_run_id WHERE r.start_time >= datetime(\"now\",\"-7 day\");
"
'
2) Import into Postgres with psql’s \copy (make sure columns match your schema; we can list explicit columns if needed)
Copy code
docker exec -it qfin-postgres-1 bash -lc "
psql -U prefect -d prefect -c \"SET session_replication_role = replica;\"
psql -U prefect -d prefect -c \"\\copy flow_run FROM '/var/lib/postgresql/data/../flow_run_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"\\copy task_run FROM '/var/lib/postgresql/data/../task_run_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"\\copy flow_run_state FROM '/var/lib/postgresql/data/../flow_run_state_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"\\copy task_run_state FROM '/var/lib/postgresql/data/../task_run_state_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"SET session_replication_role = DEFAULT;\"
"
Note: We may need to adjust the file path inside the Postgres container; the clean way is to mount your 
brrr_prefect_data
volume into the Postgres container as another mount (e.g., at 
/imports
) and then use 
/imports/flow_run_7d.csv
in the \copy commands. If you want, I’ll write the exact 
docker-compose
snippet for that. If you prefer to keep trying pgloader for full runs - Try excluding logs and events first and upping memory: - Add to EXCLUDING: 
log
and 
events
- Run with 
--memory=6g --memory-swap=6g
- You can also exclude the biggest run tables first (flow_run, task_run, run states) to finish metadata now, then decide on run history later. Quick sanity checks once you choose a path
Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM work_pool;"
If you go with the CSV run-slice, also check counts for flow_run/task_run and states. Finally, after you’re live on Postgres - Consider removing the old 
prefect_data
mount from the server (after you archive 
prefect.db
) to avoid confusion. - Keep your event settings to keep the UI snappy: - 
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
- 
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
- 
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
- 
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S
Tell me which route you want: - metadata-only now, or - metadata + last N days of runs (and how many days), or - keep pushing full pgloader with larger memory and excluding heavy tables. I’ll give you the exact commands/snippets for your choice.
o
@Marvin olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), phase, relid::regclass, bytes_processed, tuples_processed FROM pg_stat_progress_copy;" docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c \"SELECT state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';\" ERROR: column "phase" does not exist LINE 1: SELECT now(), phase, relid::regclass, bytes_processed, tuple... ^ the input device is not a TTY ": command not found
m
✅ thought for 34.8 seconds
Thanks for the updates — two quick fixes and a path to get you unblocked fast. First: the monitoring queries My bad on the columns. Use these: - COPY progress (no “phase” column in this view):
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), relid::regclass, bytes_processed, bytes_total, tuples_processed, tuples_total, elapsed_time FROM pg_stat_progress_copy;"
- Active queries:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pid, state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';"
Second: stop fighting pgloader on heavy tables — migrate metadata only Given the SBCL heap crashes and stalls, the fastest reliable path is to bring over just the Prefect metadata (deployments, blocks, pools/queues, variables, etc.) and skip runs/logs/events for now. This gets you live on Postgres with the UI snappy and avoids the biggest tables. You can always backfill a recent slice later. Use this pgloader.load:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 -- Only copy small metadata tables needed to keep working
 INCLUDING ONLY TABLE NAMES MATCHING
   'block_type', 'block_schema', 'block_schema_reference',
   'block_document', 'block_document_reference',
   'flow', 'deployment', 'deployment_version',
   'work_pool', 'work_pool_queue', 'work_queue',
   'worker', 'variable', 'concurrency_limit'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
Run it with more memory to avoid SBCL heap issues:
Copy code
docker run --rm --network qfin_default \
  --memory=6g --memory-swap=6g \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Then: - Start Prefect against Postgres (keep your event settings) and start your workers - Your deployments, blocks, pools/queues, variables, and concurrency limits will be there - Historical runs/logs/events will start fresh (good for performance) If you want some run history brought over We can import just the last N days of runs/states via CSV without stressing pgloader. Tell me “N” (e.g., 3 or 7 days) and I’ll share a ready-to-run export/import snippet that mounts your SQLite volume for export and a path into Postgres for fast \copy. Small compose cleanup (volume warning) To silence the “created for project brrr” warning:
Copy code
volumes:
  prefect_data:
    external: true
    name: brrr_prefect_data
  postgres_data:
And double-check your server env (no angle brackets):
Copy code
PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
PREFECT_API_DATABASE_MIGRATE_ON_START: "false"   # you ran upgrade manually already
PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D"
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S"
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100"
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S"
References - Server CLI (database commands) - Settings reference — server events - Settings reference — database If you prefer a different set of tables (e.g., include artifacts/automations), tell me and I’ll tailor the load file precisely.
o
@Marvin there is something wrong with your new pgloader.load. Also do i need to do anything to start fresh, or can we just use hte pgloader? ESRAP-PARSE-ERROR: At -- Only copy small metadata tables needed to keep working INCLUDING ONLY TABLE NAMES MATCHING ^ (Line 12, Column 28, Position 324) In context INCLUDING-LIKE: While parsing INCLUDING-LIKE. Expected: the character Tab or the character Newline or the character Return or the character Space or the string "--" or the string "/*" or the string "like" An unhandled error condition has been signalled: At -- Only copy small metadata tables needed to keep working INCLUDING ONLY TABLE NAMES MATCHING ^ (Line 12, Column 28, Position 324) In context INCLUDING-LIKE: While parsing INCLUDING-LIKE. Expected: the character Tab or the character Newline or the character Return or the character Space or the string "--" or the string "/*" or the string "like" What I am doing here? At -- Only copy small metadata tables needed to keep working INCLUDING ONLY TABLE NAMES MATCHING ^ (Line 12, Column 28, Position 324) In context INCLUDING-LIKE: While parsing INCLUDING-LIKE. Expected: the character Tab or the character Newline or the character Return or the character Space or the string "--" or the string "/*" or the string "like"
m
✅ thought for 41.9 seconds
Good catch — pgloader’s DSL wants LIKE, not MATCHING, and it’s picky about the exact syntax. Also: you do NOT need to fully reset Postgres; we can “start fresh” for just the tables we load by truncating them before the copy. Try this minimal “metadata‑only” load file (corrected syntax) - Copies core metadata (deployments, blocks, pools/queues, variables, etc.) - Excludes runs/logs/events to avoid the heavy/fragile tables for now - Truncates target tables first so you won’t get duplicates even if a prior attempt partially loaded Create pgloader.load with:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 INCLUDING ONLY TABLE NAMES LIKE 'block_type',
                               LIKE 'block_schema',
                               LIKE 'block_schema_reference',
                               LIKE 'block_document',
                               LIKE 'block_document_reference',
                               LIKE 'flow',
                               LIKE 'flow_tag',
                               LIKE 'deployment',
                               LIKE 'deployment_version',
                               LIKE 'work_pool',
                               LIKE 'work_pool_queue',
                               LIKE 'work_queue',
                               LIKE 'worker',
                               LIKE 'variable',
                               LIKE 'concurrency_limit'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%',
                           LIKE 'alembic_version'

 -- Ensure a clean slate for the tables we are loading
 BEFORE LOAD DO $$ TRUNCATE TABLE block_document_reference CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE block_document CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE block_schema_reference CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE block_schema CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE block_type CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE flow_tag CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE deployment_version CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE deployment CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE flow CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE work_queue CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE work_pool_queue CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE work_pool CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE worker CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE variable CASCADE; $$
 BEFORE LOAD DO $$ TRUNCATE TABLE concurrency_limit CASCADE; $$
;
Run it (give pgloader more memory to avoid SBCL heap issues):
Copy code
docker run --rm --network qfin_default \
  --memory=6g --memory-swap=6g \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Do I need to “start fresh”? - Not the whole DB. The BEFORE LOAD DO TRUNCATE statements clear just the target tables (with CASCADE) so this run is clean and duplicate‑free. - We deliberately exclude 
alembic_version
, so your migration state stays correct. Sanity checks after load
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM work_pool;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM block_document;"
Then bring Prefect up against Postgres (keep your event settings), and start workers. Your deployments/blocks/pools/queues/variables will be there; runs/logs/events will start fresh, which should materially improve UI responsiveness and memory use. If you want some recent history later (e.g., last 3–7 days of runs), I can share a CSV-based export/import that targets just those tables without stressing pgloader. Useful refs
- Server CLI (database commands): Self-hosted Server CLI - Database settings: Settings reference — database - Events settings (retention/backfill): Settings reference — server events If pgloader still complains about a specific table, paste the exact error line — I’ll tweak the include list or truncation order as needed.
o
@Marvin.... KABOOM! ESRAP-PARSE-ERROR: At INCLUDING ONLY TABLE NAMES LIKE 'block_type', LIKE 'block_schema', ^ (Line 11, Column 31, Position 298) In context ANOTHER-LIKE-EXPRESSION: While parsing LIKE-EXPRESSION. Expected: the character ' (APOSTROPHE) While parsing IGNORE-WHITESPACE. Expected: the character Tab or the character Newline or the character Return or the character Space or the string "--" or the string "/*" An unhandled error condition has been signalled: At INCLUDING ONLY TABLE NAMES LIKE 'block_type', LIKE 'block_schema', ^ (Line 11, Column 31, Position 298) In context ANOTHER-LIKE-EXPRESSION: While parsing LIKE-EXPRESSION. Expected: the character ' (APOSTROPHE) While parsing IGNORE-WHITESPACE. Expected: the character Tab or the character Newline or the character Return or the character Space or the string "--" or the string "/*" What I am doing here? At INCLUDING ONLY TABLE NAMES LIKE 'block_type', LIKE 'block_schema', ^ (Line 11, Column 31, Position 298) In context ANOTHER-LIKE-EXPRESSION: While parsing LIKE-EXPRESSION. Expected:
m
✅ thought for 26.6 seconds
You’re right — my last pgloader file used the wrong syntax. pgloader doesn’t support a comma-separated list of LIKEs on one line; you either repeat the INCLUDING line or use a single MATCHING regex. Let’s keep it simple and get you unblocked with a “metadata-only” copy that: - includes deployments/blocks/pools/queues/variables/etc. - excludes runs/logs/events - truncates the target tables first (no need to “start fresh” DB-wide) Use this pgloader.load (valid syntax)
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 INCLUDING ONLY TABLE NAMES LIKE 'block_%'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
 INCLUDING ONLY TABLE NAMES LIKE 'flow'
 INCLUDING ONLY TABLE NAMES LIKE 'flow_tag'
 INCLUDING ONLY TABLE NAMES LIKE 'variable'
 INCLUDING ONLY TABLE NAMES LIKE 'concurrency_limit'
 INCLUDING ONLY TABLE NAMES LIKE 'worker'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
Run it (give pgloader more memory to avoid SBCL heap issues):
Copy code
docker run --rm --network qfin_default \
  --memory=6g --memory-swap=6g \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Notes - WITH truncate makes pgloader empty the included target tables for you — you don’t need to reset/drop the whole DB to “start fresh”. - If you still see heap issues, drop batch/prefetch to 100/100 or bump memory higher. - The earlier “stuck” messages are often pgloader busy rather than hung; you can check Postgres activity with: - Progress: 
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), relid::regclass, bytes_processed, bytes_total, tuples_processed, tuples_total, elapsed_time FROM pg_stat_progress_copy;"
- Active queries: 
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pid, state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';"
Sanity checks after load
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM work_pool;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM block_document;"
Then bring Prefect up against Postgres (keep your event settings) and start the workers. You’ll retain deployments, blocks, pools/queues, variables, etc., and start fresh on runs/logs/events — which should immediately improve UI performance and memory usage. If you later want a small window of run history (e.g., last 3–7 days), I can give you a CSV export/import that targets just flow_run/task_run and their states without stressing pgloader. Tell me how many days you’d like and I’ll tailor the commands.
o
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), relid::regclass, bytes_processed, bytes_total, tuples_processed, tuples_total, elapsed_time FROM pg_stat_progress_copy;" fails for tuples_total and elapsed_time missing, aslo have no idea what i am looking at in the active queries @Marvin
m
✅ thought for 24.7 seconds
Good catch — those columns vary by Postgres version. - Check your version:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT version();"
- Version-agnostic copy progress:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT now(),
       relid::regclass,
       bytes_processed,
       bytes_total,
       tuples_processed
FROM pg_stat_progress_copy;"
If you see rows here, the copy is running. If you see nothing, pgloader is not actively copying at that moment. - Active queries (simplified):
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT pid, state, wait_event_type, wait_event,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = 'prefect' AND state <> 'idle'
ORDER BY query_start DESC
LIMIT 10;"
What to look for: - COPY statements like 
COPY public.deployment FROM STDIN
→ data is moving - If nothing shows for minutes while pgloader “warns”, it’s likely stalled (often memory/transform overhead) On “start fresh” - You do NOT need to drop the whole DB again. Using pgloader with: - 
WITH truncate
or - explicit 
BEFORE LOAD DO TRUNCATE
statements …will clear just the target tables before copying, which is enough to avoid duplicates. If pgloader still stalls - The most reliable unblock is the “metadata-only” import (no runs/logs/events). Use the corrected load file syntax that pgloader accepts (one INCLUDING per line, using LIKE) and let pgloader truncate those targets:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 INCLUDING ONLY TABLE NAMES LIKE 'block_%'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
 INCLUDING ONLY TABLE NAMES LIKE 'flow'
 INCLUDING ONLY TABLE NAMES LIKE 'flow_tag'
 INCLUDING ONLY TABLE NAMES LIKE 'variable'
 INCLUDING ONLY TABLE NAMES LIKE 'concurrency_limit'
 INCLUDING ONLY TABLE NAMES LIKE 'worker'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
Run with extra memory to prevent SBCL heap issues:
Copy code
docker run --rm --network qfin_default \
  --memory=6g --memory-swap=6g \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
Then sanity-check the counts:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname IN ('deployment','deployment_version','flow','flow_tag','block_document','block_document_reference','block_schema','block_schema_reference','block_type','work_pool','work_pool_queue','work_queue','worker','variable','concurrency_limit')
ORDER BY relname;"
If those look reasonable, start Prefect against Postgres and bring your workers up. You’ll have deployments/blocks/pools/queues/variables ready to go; runs/logs/events will start fresh (which should help the UI and memory immediately). If you’d like recent run history too, tell me how many days (e.g., last 3/7 days) and I’ll give you a CSV-based export/import for just flow/task runs and their states without stressing pgloader. References: - Self-hosted Server CLI - Settings reference — database - Settings reference — server events
o
@Marvin when i query pg_stat_progress_copy repeatedly the bytes_processed and tuples_processed numbers are not moving. When i run the active thing it looks like copy events and UPDATE flow_run is still present. To me that looks like hanging stuff from the old failed runs
m
✅ thought for 34.5 seconds
You’re right: if pg_stat_progress_copy isn’t moving and you still see COPY events / UPDATE flow_run in pg_stat_activity, you’ve got stuck backends from prior attempts. Here’s the quickest way to clear them and get a clean “metadata-only” import through: 1) Stop any running pgloader container
Copy code
docker ps | grep pgloader
docker kill <container_id>
2) Kill stuck Postgres backends - List active queries
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = 'prefect' AND state <> 'idle'
ORDER BY query_start;"
"
- Cancel or terminate them (cancel first; if they don’t stop in ~10s, terminate)
Copy code
-- cancel
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE datname='prefect' AND state <> 'idle';"
-- terminate
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='prefect' AND state <> 'idle';"
- If anything refuses to die, just restart Postgres:
Copy code
docker restart qfin-postgres-1
3) Ensure heavy/partially loaded tables are empty You do NOT need to reset the whole DB. Truncate only the heavy run/log/event tables so we start fresh on those and avoid conflicts:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
TRUNCATE TABLE
  events,
  log,
  flow_run_state,
  task_run_state,
  task_run,
  flow_run
CASCADE;"
4) Run a metadata-only pgloader job (no runs/logs/events) Use this load file (valid syntax). It includes deployments/blocks/pools/queues/variables and excludes runs/logs/events:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 INCLUDING ONLY TABLE NAMES LIKE 'block_%'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
 INCLUDING ONLY TABLE NAMES LIKE 'flow'
 INCLUDING ONLY TABLE NAMES LIKE 'flow_tag'
 INCLUDING ONLY TABLE NAMES LIKE 'variable'
 INCLUDING ONLY TABLE NAMES LIKE 'concurrency_limit'
 INCLUDING ONLY TABLE NAMES LIKE 'worker'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
Run it with extra memory so SBCL doesn’t choke:
Copy code
docker run --rm --network qfin_default \
  --memory=6g --memory-swap=6g \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
5) Sanity-check and bring Prefect up - Quick counts:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname IN ('deployment','deployment_version','flow','flow_tag',
                  'block_type','block_schema','block_schema_reference',
                  'block_document','block_document_reference',
                  'work_pool','work_pool_queue','work_queue','worker',
                  'variable','concurrency_limit')
ORDER BY relname;"
"
- Start the server and workers (ensure no angle brackets in the URL and keep your event settings). Notes - Seeing COPY events in activity means an old pgloader was still copying; killing those backends (or restarting Postgres) clears it. - The JSON vs JSONB warnings are harmless. - If you later want a slice of recent run history, I can give you CSV export/import steps for, say, last 3–7 days of flow/task runs + states without stressing pgloader. Want me to generate a CSV-based import for the last N days of runs? If yes, tell me N and I’ll paste exact commands.
o
@Marvin looking better. However deployment shows 0 - despite us having 30 flows. Is that an issue? I had quite a few deployments, prefer not to recreate those and the schedules. also need commands to restart server etc.
m
✅ thought for 119.8 seconds
Thanks for the status update. Two things to tackle: 1) Why deployments show 0 - That means the deployment table didn’t get copied in your last pgloader pass, or it was truncated but not refilled due to the includes/excludes. - We can import just the deployment-related tables quickly without touching runs/logs/events. Do this to load your deployments (and schedules) from SQLite to Postgres: - Create a focused pgloader file for deployment metadata only:
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 INCLUDING ONLY TABLE NAMES LIKE 'flow'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment_version'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment_schedule'
 INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_queue'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'
;
- Run it with extra memory:
Copy code
docker run --rm --network qfin_default \
  --memory=6g --memory-swap=6g \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
- Sanity check:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment_schedule;"
- In the UI or with CLI:
Copy code
prefect deployments ls
If the run still doesn’t copy deployments: - It may be foreign key ordering (e.g., missing flows). The above includes 
flow
and 
work_pool/work_queue
to satisfy FKs. If you have infrastructure/storage block documents attached to deployments, we should include block tables too: Add:
Copy code
INCLUDING ONLY TABLE NAMES LIKE 'block_%'
and re-run. 2) How to restart or manage the server and services - Restart only the server container:
Copy code
docker compose restart prefect-server
- Bring the server down/up:
Copy code
docker compose down
docker compose up -d prefect-server
- Restart Postgres:
Copy code
docker restart qfin-postgres-1
- Check server health:
Copy code
curl -s <http://localhost:4200/api/health>
- Verify deployments via CLI (from any machine with PREFECT_API_URL pointing to your server):
Copy code
prefect deployments ls
prefect deployments inspect "<flow-name>/<deployment-name>"
prefect deployments schedule list "<flow-name>/<deployment-name>"
Pro tip: ensure your CLI is pointing at your self-hosted server - Set:
Copy code
export PREFECT_API_URL=<http://localhost:4200/api>
- Then:
Copy code
prefect deployments ls
If you’d like, paste the output of: - 
SELECT COUNT(*) FROM flow;
- 
SELECT COUNT(*) FROM work_pool;
- 
SELECT COUNT(*) FROM work_queue;
- 
SELECT COUNT(*) FROM block_document;
- 
SELECT COUNT(*) FROM deployment;
- 
SELECT COUNT(*) FROM deployment_schedule;
I can adjust the pgloader include set to pull exactly what’s missing, then you can restart the server and your existing schedules should just pick back up.
o
@Marvin so the flows and work pools etc are present, the deployments are still missing, but by select count and cli. naturally also the schedules. very annoying. i am deploying from source (using blocks / git) if that matters
m
✅ thought for 58.8 seconds
Got it — flows and pools are present, but deployments (and schedules) are still empty. Since you deploy from source (git blocks), let’s get those deployments back without recreating by hand. First, confirm the old SQLite actually has deployments
Copy code
docker run --rm -v brrr_prefect_data:/data alpine sh -lc '
apk add --no-cache sqlite;
sqlite3 /data/prefect.db "SELECT COUNT(*) FROM deployment;"
sqlite3 /data/prefect.db "SELECT COUNT(*) FROM deployment_schedule;"
'
If both are > 0, we can copy just those tables. Targeted pgloader for deployments (with FK-friendly settings) 1) Clear just the deployment tables in Postgres so we start clean:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
TRUNCATE TABLE deployment_schedule, deployment_version, deployment CASCADE;"
2) Create pgloader.load with this content (syntax is finicky; one INCLUDING per line):
Copy code
LOAD DATABASE
     FROM sqlite:///root/.prefect/prefect.db
     INTO <postgresql://prefect:prefect@postgres:5432/prefect>

 WITH data only,
      truncate,
      batch rows = 200,
      prefetch rows = 200,
      on error stop

 INCLUDING ONLY TABLE NAMES LIKE 'flow'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment_version'
 INCLUDING ONLY TABLE NAMES LIKE 'deployment_schedule'
 INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
 INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
 INCLUDING ONLY TABLE NAMES LIKE 'block_%'

 EXCLUDING TABLE NAMES LIKE 'sqlite_%'
 EXCLUDING TABLE NAMES LIKE 'alembic_version'

 -- Temporarily relax constraints so FK ordering does not block the load
 BEFORE LOAD DO $$ SET session_replication_role = 'replica'; $$
 AFTER LOAD DO  $$ SET session_replication_role = 'origin'; $$
;
3) Run it (give pgloader memory):
Copy code
docker run --rm --network qfin_default \
  --memory=6g --memory-swap=6g \
  -v brrr_prefect_data:/root/.prefect \
  -v $(pwd)/pgloader.load:/load.load \
  dimitri/pgloader:latest pgloader /load.load
4) Sanity check:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment_schedule;"
Then from your CLI (with PREFECT_API_URL pointing at your server):
Copy code
prefect deployments ls
If deployment counts in SQLite are > 0 but still 0 in Postgres after this run - It’s likely a data shape mismatch that pgloader doesn’t like. In that case, the fastest path is to re-register deployments from source (since you already deploy from git). This preserves schedules too if they’re defined in your project or deploy code. Two ways to re-register from source - Prefect project(s): - In each project directory that contains your 
.prefect
config, run:
Copy code
prefect deploy
- This will recreate deployments and schedules defined in your project configuration. - From code using from_source + deploy: - If you define deployments in code, re-run your deployment script; e.g.:
Copy code
from prefect import flow
    flow.from_source("<https://github.com/owner/repo>", entrypoint="path/to/file.py:my_flow").deploy(
        name="my-deployment",
        work_pool_name="production-pool",
        schedules=[...],
        parameters={...},
    )
- Re-running this for each flow will recreate the deployments and their schedules in Postgres. I can help generate one-off scripts if you can share how your deployments are defined (Prefect project vs in-code deploy calls). If you have a project repo, pointing me at where deployments are declared is enough for me to suggest exact commands. Server management commands you asked for - Restart server:
Copy code
docker compose restart prefect-server
- Stop/start server:
Copy code
docker compose down
docker compose up -d prefect-server
- Restart Postgres:
Copy code
docker restart qfin-postgres-1
- Check health:
Copy code
curl -s <http://localhost:4200/api/health>
- Ensure your CLI points to this server: ```
export PREFECT_API_URL=http://localhost:4200/api prefect deployments ls ``` Your call on next step - Try the targeted pgloader load above for deployments. If it still won’t copy, I recommend re-registering deployments from source — it’s quick and keeps your schedules intact as they’re defined in code/project. If you confirm which method you used originally (project vs code), I’ll give you exact commands or a small script to recreate all 30 in one shot.
o
UGH
@Marvin after migrating and deploying manually i get the following when trying to run deployment Worker 'DockerWorker 185c9cea-2f4d-4fdb-9414-a7a35be5c1c0' submitting flow run '01cbe7ce-1463-4b21-b746-688e309933f9' 025808 PM prefect.flow_runs.worker Completed submission of flow run '01cbe7ce-1463-4b21-b746-688e309933f9' 025811 PM prefect.flow_runs.worker Opening process... 025821 PM prefect.flow_runs.runner > Running git_clone step... 025826 PM prefect.flow_runs Engine execution exited with unexpected exception Traceback (most recent call last): File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 1552, in run_flow ret_val = run_flow_sync(**kwargs) ^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 1394, in run_flow_sync with engine.run_context(): ^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/contextlib.py", line 137, in enter return next(self.gen) ^^^^^^^^^^^^^^ File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 772, in run_context with self.setup_run_context(): ^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/contextlib.py", line 137, in enter return next(self.gen) ^^^^^^^^^^^^^^ File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 603, in setup_run_context self.flow_run = client.read_flow_run(self.flow_run.id) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/orchestration/_flow_runs/client.py", line 218, in read_flow_run response = self.request( ^^^^^^^^^^^^^ File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/orchestration/base.py", line 33, in request return self._client.send(request) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/base.py", line 622, in send response.raise_for_status() File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/base.py", line 163, in raise_for_status raise PrefectHTTPStatusError.from_httpx_error(exc) from exc.cause prefect.exceptions.PrefectHTTPStatusError: Server error '500 Internal Server Error' for url 'http://host.docker.internal:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9' Response: {'exception_message': 'Internal Server Error'} For more information check: https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/500 025832 PM prefect.flow_runs Process for flow run 'tidy-bat' exited with status code: 1
m
✅ thought for 40.2 seconds
Thanks — that 500 is coming from the server. The client stack shows a simple GET of the flow run by id, so we need the server traceback to pinpoint it. Quick checks to isolate the cause 1) Get the server traceback around the time of the failure - Replace the container name with yours if different:
Copy code
docker logs --since=15m qfin-prefect-server-1
Paste the Python traceback you see for the GET /api/flow_runs/<id> request. 2) Confirm the run row looks sane in Postgres - Replace the ID with the failing one:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT id, state_type, start_time, end_time, total_run_time
FROM flow_run
WHERE id = '01cbe7ce-1463-4b21-b746-688e309933f9';"
- We’re checking that the row exists and 
total_run_time
is an INTERVAL (not TEXT) and not some malformed value. 3) Verify server and worker are on the same Prefect version - Ask the server:
Copy code
curl -s <http://localhost:4200/api/version>
- Check your worker image tag. If you’re using 
3-latest
, it can drift. It’s best to pin both server and workers to the same exact version you see above (e.g., 
prefecthq/prefect:3.4.17
or whatever the server reports). Mismatches can surface as 500s due to schema/model diffs. 4) Sanity-check the API from the host - Replace the id:
Copy code
curl -i <http://localhost:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9>
If this also returns 500, it’s definitely a server-side exception we’ll catch in step 1 logs. Common culprits after migration - A schema mismatch due to version drift (server vs worker), or an out-of-date server image pulled mid-process - An extension or type issue (e.g., interval, jsonb) — the traceback will tell us which column - A related resource missing (e.g., a deployment schedule or block doc referenced by the flow run) — again, the server logs will make this clear Restart/management commands you asked for - Restart the server:
Copy code
docker compose restart prefect-server
- Stop and start the server:
Copy code
docker compose down
docker compose up -d prefect-server
- Restart Postgres:
Copy code
docker restart qfin-postgres-1
- Health:
Copy code
curl -s <http://localhost:4200/api/health>
- CLI to list deployments:
Copy code
export PREFECT_API_URL=<http://localhost:4200/api>
prefect deployments ls
Next step - Please paste the server logs traceback for the 500, plus the results of the psql query on the flow_run row and the server’s /api/version. With that, I can give you a precise fix (e.g., pin versions, adjust a column type, or re-create a missing related record).
o
@Marvin tracebacks are too long to paste in total, but sdeems to revolve around " File "/usr/local/lib/python3.12/site-packages/prefect/server/utilities/server.py", line 50, in handle_response_scoped_depends response = await default_handler(request) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/fastapi/routing.py", line 302, in app raw_response = await run_endpoint_function( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/fastapi/routing.py", line 213, in run_endpoint_function return await dependant.call(**values) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/server/api/flow_runs.py", line 334, in read_flow_run return schemas.responses.FlowRunResponse.model_validate( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/prefect/server/schemas/responses.py", line 320, in model_validate response = super().model_validate(obj) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/pydantic/main.py", line 705, in model_validate return cls.__pydantic_validator__.validate_python( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ pydantic_core._pydantic_core.ValidationError: 1 validation error for FlowRunResponse estimated_run_time Error extracting attribute: TypeError: can only concatenate str (not "Interval") to str [type=get_attribute_error, input_value=FlowRun(id=01cbe7ce-1463-4b21-b746-688e309933f9), input_type=FlowRun] For further information visit https://errors.pydantic.dev/" cant find the request n it self for the postgres: state_type is running, total_run_time 000000 http://localhost:4200/api/version lists 0.8.4 which seems very weird? http://localhost:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9 gives Internal Server Error as exception message
m
✅ thought for 47.3 seconds
Thanks — the traceback points to a server-side serialization error when building FlowRunResponse: Pydantic is encountering a non-Python timedelta for a duration field and choking. Given your prior pgloader attempts, the most likely cause is a type mismatch on interval fields in the flow_run/task_run tables. Let’s verify and fix that, then align versions and retry. 1) Verify interval column types in Postgres Run these and share results:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'flow_run' AND column_name IN ('total_run_time');"

docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'task_run' AND column_name IN ('total_run_time');"
Expected: both should be 
interval
. If either shows 
text
(or anything else), fix them:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
ALTER TABLE flow_run
  ALTER COLUMN total_run_time TYPE interval
  USING NULLIF(total_run_time::text, '')::interval;"

docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
ALTER TABLE task_run
  ALTER COLUMN total_run_time TYPE interval
  USING NULLIF(total_run_time::text, '')::interval;"
2) Ensure server and workers run the same Prefect 3 image tag - You mentioned /api/version returns 0.8.4 — that’s the internal server package version and can be okay, but to avoid drift, pin both server and workers to the same Prefect 3 tag. - In docker-compose, set the same tag everywhere (example):
Copy code
image: prefecthq/prefect:3.4.17
- Recreate only the server and workers with the pinned tag:
Copy code
docker compose up -d --force-recreate --no-deps prefect-server
docker compose up -d --force-recreate --no-deps prefect-prod-worker prefect-dev-worker
- From inside the server container, confirm:
Copy code
docker exec -i qfin-prefect-server-1 prefect version
Confirm worker version similarly:
Copy code
docker exec -i <worker-container-name> prefect version
3) Confirm you are using asyncpg driver - Check the env in your server container:
Copy code
docker exec -i qfin-prefect-server-1 printenv | grep PREFECT_API_DATABASE_CONNECTION_URL
It must be:
Copy code
<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
4) Restart server and retry the run - Restart the server cleanly and test the problematic API endpoint:
Copy code
docker compose restart prefect-server
curl -i <http://localhost:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9>
- Try a small test deployment/flow run again. If you still get a 500, paste the top of the new traceback — it should shift if the interval fix worked. 5) Double-check flow run row integrity - Sanity on the specific run:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT id, state_type, start_time, end_time, total_run_time
FROM flow_run
WHERE id = '01cbe7ce-1463-4b21-b746-688e309933f9';"
- If the run exists and 
total_run_time
is a proper interval (e.g., 000000), the server should serialize it to a timedelta without error. 6) If deployments were re-registered from source - Make sure your CLI points to your server:
Copy code
export PREFECT_API_URL=<http://localhost:4200/api>
prefect deployments ls
- Run one deployment:
Copy code
prefect deployments run "<flow-name>/<deployment-name>"
Server/worker management commands (quick reference) - Restart server:
Copy code
docker compose restart prefect-server
- Down/up server:
Copy code
docker compose down
docker compose up -d prefect-server
- Restart Postgres:
Copy code
docker restart qfin-postgres-1
- Health:
Copy code
curl -s <http://localhost:4200/api/health>
If the error persists after ensuring interval columns are correct and versions align, please paste: - The output of the two information_schema queries above - The top 30–40 lines of the new server traceback around read_flow_run - The output of 
prefect version
from server and worker containers
Links for reference: - Server CLI (database): Self-hosted Server CLI - DB settings (asyncpg URL, timeouts): Settings reference — database - Events settings (keep your shorter retention/backfill): Settings reference — server events
79 Views
Open in Slack
PreviousNext
Powered by